﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.Common;
using MySql.Data;
using MySql.Data.MySqlClient;
using Entities;

namespace DAL
{
    public class StockDAL
    {
        private static MySqlConnection SqlConnection1;
        private static MySqlDataAdapter SqlDataAdapter1;
        private static MySqlCommand SqlSelectCommand1;
        private static MySqlCommand SqlInsertCommand1;
        private static MySqlCommand SqlUpdateCommand1;
        public static DataSet dt;

        public static DataSet CrearDataset()
        {
            MySqlDataAdapter da = AdaptadorSELECT();
            dt = new DataSet();
            da.Fill(dt, "Stock");
            return dt;
        }

        public static DataSet CrearDataset(string whereLocales, int proveedor, string articulo, string descripcion)
        {
            MySqlDataAdapter da = AdaptadorSELECT(whereLocales, proveedor, articulo, descripcion);
            dt = new DataSet();
            da.Fill(dt, "Stock");
            return dt;
        }

        public static void InsertarDT(DataTable tabla, Stock entidad)
        {
            DataRowCollection cfilas = tabla.Rows;
            DataRow nuevaFila;
            try
            {
                nuevaFila = tabla.NewRow();
                nuevaFila[0] = entidad.IdArticulo.ToString();
                nuevaFila[1] = entidad.IdLocal.ToString();
                nuevaFila[2] = entidad.Cantidad;
                cfilas.Add(nuevaFila);
            }
            catch (ConstraintException ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        public static void EntradasDT(DataSet dsStock, DataView detalle, int local)
        {
            DataView viewStock = new DataView(dsStock.Tables[0]);
            foreach (DataRowView fila in detalle)
            {
                string articulo = fila["IdArticuloMSTKD"].ToString();
                if (articulo == "") return;
                int cantidad = Convert.ToInt32(fila["CantidadMSTKD"].ToString());                
                viewStock.RowFilter = "IdArticuloSTK = '" + articulo + "' AND IdLocalSTK = '" + local + "'";
                DataRowView rowView = viewStock[0];
                int cantidadAnterior = Convert.ToInt32(rowView["CantidadSTK"].ToString());
                int cantidadActual = cantidadAnterior + cantidad;
                rowView.BeginEdit();
                rowView["CantidadSTK"] = cantidadActual;
                rowView.EndEdit();
            }
        }

        public static void SalidasDT(DataSet dsStock, DataView detalle, int local)
        {
            DataView viewStock = new DataView(dsStock.Tables[0]);
            foreach (DataRowView fila in detalle)
            {
                string articulo = fila["IdArticuloMSTKD"].ToString();
                if (articulo == "") return;
                int cantidad = Convert.ToInt32(fila["CantidadMSTKD"].ToString());
                viewStock.RowFilter = "IdArticuloSTK = '" + articulo + "' AND IdLocalSTK = '" + local + "'";
                DataRowView rowView = viewStock[0];
                int cantidadAnterior = Convert.ToInt32(rowView["CantidadSTK"].ToString());
                int cantidadActual = cantidadAnterior - cantidad;
                rowView.BeginEdit();
                rowView["CantidadSTK"] = cantidadActual;
                rowView.EndEdit();
            }
        }

        public static void SalidasVentasDT(DataSet dsStock, DataView detalle, int local)
        {
            DataView viewStock = new DataView(dsStock.Tables[0]);
            foreach (DataRowView fila in detalle)
            {
                string articulo = fila["IdArticuloDVEN"].ToString();
                if (articulo == "") return;
                int cantidad = Convert.ToInt32(fila["CantidadDVEN"].ToString());
                viewStock.RowFilter = "IdArticuloSTK = '" + articulo + "' AND IdLocalSTK = '" + local + "'";
                if (viewStock.Count > 0)
                {
                    DataRowView rowView = viewStock[0];
                    int cantidadAnterior = Convert.ToInt32(rowView["CantidadSTK"].ToString());
                    int cantidadActual = cantidadAnterior - cantidad;
                    rowView.BeginEdit();
                    rowView["CantidadSTK"] = cantidadActual;
                    rowView.EndEdit();
                }
            }
        }

        public static void EntradasVentasDT(DataSet dsStock, DataView detalle, int local)
        {
            DataView viewStock = new DataView(dsStock.Tables[0]);
            foreach (DataRowView fila in detalle)
            {
                string articulo = fila["IdArticuloDVEN"].ToString();
                if (articulo == "") return;
                int cantidad = Convert.ToInt32(fila["CantidadDVEN"].ToString());
                viewStock.RowFilter = "IdArticuloSTK = '" + articulo + "' AND IdLocalSTK = '" + local + "'";
                if (viewStock.Count > 0)
                {
                    DataRowView rowView = viewStock[0];
                    int cantidadAnterior = Convert.ToInt32(rowView["CantidadSTK"].ToString());
                    int cantidadActual = cantidadAnterior + cantidad;
                    rowView.BeginEdit();
                    rowView["CantidadSTK"] = cantidadActual;
                    rowView.EndEdit();
                }
            }
        }

        public static void GrabarDB(DataSet dt, MySqlConnection conn, MySqlTransaction tr)
        {
            MySqlDataAdapter da = AdaptadorABM(conn, tr);
            da.Update(dt, "Stock");
        }

        private static MySqlDataAdapter AdaptadorSELECT()
        {
            SqlConnection1 = DALBase.GetConnection();
            SqlDataAdapter1 = new MySqlDataAdapter();
            SqlSelectCommand1 = new MySqlCommand("Stock_Listar", SqlConnection1);
            SqlDataAdapter1.SelectCommand = SqlSelectCommand1;
            SqlSelectCommand1.CommandType = CommandType.StoredProcedure;
            return SqlDataAdapter1;
        }

        private static MySqlDataAdapter AdaptadorSELECT(string whereLocales, int proveedor, string articulo, string descripcion)
        {
            SqlConnection1 = DALBase.GetConnection();
            SqlDataAdapter1 = new MySqlDataAdapter();
            SqlSelectCommand1 = new MySqlCommand("Stock_Cons", SqlConnection1);
            SqlDataAdapter1.SelectCommand = SqlSelectCommand1;
            SqlSelectCommand1.Parameters.AddWithValue("p_locales", whereLocales);
            SqlSelectCommand1.Parameters.AddWithValue("p_proveedor", proveedor);
            SqlSelectCommand1.Parameters.AddWithValue("p_articulo", articulo);
            SqlSelectCommand1.Parameters.AddWithValue("p_descripcion", descripcion);
            SqlSelectCommand1.Parameters.AddWithValue("p_activoWeb", 0);
            SqlSelectCommand1.CommandType = CommandType.StoredProcedure;
            return SqlDataAdapter1;
        }        

        private static MySqlDataAdapter AdaptadorABM(MySqlConnection SqlConnection1, MySqlTransaction tr)
        {
            SqlDataAdapter1 = new MySqlDataAdapter();
            SqlInsertCommand1 = new MySqlCommand("Stock_Insertar", SqlConnection1);
            SqlUpdateCommand1 = new MySqlCommand("Stock_Actualizar", SqlConnection1);
            SqlInsertCommand1.Transaction = tr;
            SqlUpdateCommand1.Transaction = tr;
            SqlDataAdapter1.InsertCommand = SqlInsertCommand1;
            SqlDataAdapter1.UpdateCommand = SqlUpdateCommand1;


            // IMPLEMENTACIÓN DE LA ORDEN INSERT
            SqlInsertCommand1.Parameters.Add("p_id_articulo", MySqlDbType.VarChar, 50, "IdArticuloSTK");
            SqlInsertCommand1.Parameters.Add("p_id_local", MySqlDbType.Int32, 11, "IdLocalSTK");
            SqlInsertCommand1.Parameters.Add("p_cantidad", MySqlDbType.Int32, 11, "CantidadSTK");
            SqlInsertCommand1.CommandType = CommandType.StoredProcedure;

            // IMPLEMENTACIÓN DE LA ORDEN UPDATE
            SqlUpdateCommand1.Parameters.Add("p_id_articulo", MySqlDbType.VarChar, 50, "IdArticuloSTK");
            SqlUpdateCommand1.Parameters.Add("p_id_local", MySqlDbType.Int32, 11, "IdLocalSTK");
            SqlUpdateCommand1.Parameters.Add("p_cantidad", MySqlDbType.Int32, 11, "CantidadSTK");
            SqlUpdateCommand1.CommandType = CommandType.StoredProcedure;

            return SqlDataAdapter1;
        }

    }

}
